﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace FMixPlayer
{
    public partial class FormMain : Form
    {
        //Строка подключения к бд, сдесь указаны ip-адресс СУБД, данные аутентификации и кодировка
        static public string Connect = "Database=test;Data Source=100.0.0.1;charset=utf8;User Id=client;Password=client";
        
        public static MySqlConnection globalConnection;
        public static DataTable dTable;

        static public List<Task> Tasks = new List<Task>();
        static public List<User> Users = new List<User>();
        static public List<Status> Statuses = new List<Status>();
        static public List<Project> Projects = new List<Project>();

        static public String CurrentUser;
        static public String currentProject;

        public FormMain(string username)
        {
            InitializeComponent();
            //Подключение к БД, сам экземпляр подключения является статическим и доступен из любого места программы
            globalConnection = new MySqlConnection(Connect);
            globalConnection.Open();

            readAll();

            this.Text =  "Задачи пользователя " + username;
            CurrentUser = username;
        }

       public void readAll()
        {
            //Заполнить массивы данными из БД
            readProjects();
            readUserTasks();
            readUsers();
            readStatuses();
            //
        }


        public void updateAll()//Вызывается при необходимости обновить информацию на главном окне
        {
            readUserTasks();
        }

        void readStatuses(){//Прочесть список возможных статусов из БД

            string CommandGetStatuses = "SELECT * FROM statuses";

            MySqlCommand GetStatusesTable = new MySqlCommand(CommandGetStatuses, globalConnection);

            DataTable dTable = new DataTable();
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(CommandGetStatuses, globalConnection);

            dataAdapter.Fill(dTable);

            DataGridView tempGrid = new DataGridView();
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dTable;



            this.Controls.Add(tempGrid);
            tempGrid.DataSource = bSource;

            Statuses = new List<Status>();
            int statusesNum = dTable.Rows.Count;


            for (int i = 0; i <= statusesNum - 1; i++)
            {
                Statuses.Add(new Status(Convert.ToInt16(tempGrid.Rows[i].Cells[0].Value),
                    tempGrid.Rows[i].Cells[1].Value.ToString()));
            }
            this.Controls.Remove(tempGrid);
            tempGrid = null;
        }

        void readUsers()//Прочесть список пользователей из БД
        {
            string CommandGetUsers = "SELECT * FROM users";


            MySqlCommand GetUsersTable = new MySqlCommand(CommandGetUsers, globalConnection);

            dTable = new DataTable();
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(CommandGetUsers, globalConnection);

            dataAdapter.Fill(dTable);

            DataGridView tempGrid = new DataGridView();
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dTable;

            
            
            this.Controls.Add(tempGrid);
            tempGrid.DataSource = bSource;


            
             int usersNum = dTable.Rows.Count;
             Users = new List<User>();
            
             for (int i = 0; i <= usersNum - 1; i++)
             {
                 Users.Add(new User(Convert.ToInt16(tempGrid.Rows[i].Cells[0].Value),
                     tempGrid.Rows[i].Cells[1].Value.ToString(), Convert.ToInt16(tempGrid.Rows[i].Cells[3].Value)));
             }
             this.Controls.Remove(tempGrid);
             tempGrid = null;
        }

        void readUserTasks()//Чтение задач для текущего пользователя из БД
        {
            //string CommandGetTable = "SELECT * FROM tasks";
            string CommandGetTable = "SELECT * FROM "+comboBoxProjects.Text;

            MySqlCommand GetTable = new MySqlCommand(CommandGetTable, globalConnection);
            //

            DataTable dTable = new DataTable();
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(CommandGetTable, globalConnection);

            dataAdapter.Fill(dTable);

            BindingSource bSource = new BindingSource();

            bSource.DataSource = dTable;

            dataGridView1.DataSource = bSource;

            //
            dataGridView1.Columns[4].Visible = false;

            dataGridView1.Columns[9].Visible = false;

            dataGridView1.Columns[0].HeaderCell.Value = "№";
            dataGridView1.Columns[1].HeaderCell.Value = "Имя";
            dataGridView1.Columns[2].HeaderCell.Value = "Создал";
            dataGridView1.Columns[3].HeaderCell.Value = "Исполнитель";
            dataGridView1.Columns[5].HeaderCell.Value = "Начало";
            dataGridView1.Columns[6].HeaderCell.Value = "Конец";
            dataGridView1.Columns[7].HeaderCell.Value = "% готово";
            dataGridView1.Columns[8].HeaderCell.Value = "Статус";
            //

            Tasks = new List<Task>();

            int tasksNum = dataGridView1.RowCount;

            for(int i=0;i<=tasksNum-1;i++){
                Tasks.Add(new Task(Convert.ToInt16(dataGridView1.Rows[i].Cells[0].Value),
                    dataGridView1.Rows[i].Cells[1].Value.ToString(),
                    dataGridView1.Rows[i].Cells[2].Value.ToString(),
                    dataGridView1.Rows[i].Cells[3].Value.ToString(),
                    dataGridView1.Rows[i].Cells[4].Value.ToString(),
                    dataGridView1.Rows[i].Cells[5].Value.ToString(),
                    dataGridView1.Rows[i].Cells[6].Value.ToString(),
                    Convert.ToInt16(dataGridView1.Rows[i].Cells[7].Value),
                    dataGridView1.Rows[i].Cells[8].Value.ToString(),
                    dataGridView1.Rows[i].Cells[9].Value.ToString()));
               
                
            }
            //Выставляем у элементов фильтров значение "Все"

            comboBoxFilter1.SelectedItem = comboBoxFilter1.Items[comboBoxFilter1.Items.Count-1];
            comboBoxTime.SelectedItem = comboBoxTime.Items[comboBoxTime.Items.Count - 1];
            comboBoxStatus.SelectedItem = comboBoxStatus.Items[comboBoxStatus.Items.Count - 1];
        }
        
        //Создание нового проекта в БД
        public void createProject(string _name)
        {
            //string _name = "testproject";
            string addProjectCommand = "CREATE TABLE `"+_name+"` (`id` bigint(11) auto_increment, `name` text character set cp1251 NOT NULL, `creator` text character set cp1251 NOT NULL, `executor` text character set cp1251 NOT NULL, `comment` text character set cp1251 NOT NULL, `starttime` date NOT NULL, `endtime` date NOT NULL, `completed` int(11) NOT NULL default '0', `status` text character set cp1251 NOT NULL, `temp` text collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`))";
            MySqlCommand addProjectTask = new MySqlCommand(addProjectCommand, globalConnection);
            addProjectTask.ExecuteScalar();

            string addProjectReferenceCommand = "INSERT INTO projects (name) VALUES ('"+_name+"')";
            MySqlCommand addProjectreferenceTask = new MySqlCommand(addProjectReferenceCommand, globalConnection);
            addProjectreferenceTask.ExecuteScalar();
            /*
            CREATE TABLE `tasks` (
          `id` bigint(11) NOT NULL auto_increment,
        `name` text character set cp1251 NOT NULL,
        `creator` text character set cp1251 NOT NULL,
        `executor` text character set cp1251 NOT NULL,
        `comment` text character set cp1251 NOT NULL,
        `starttime` date NOT NULL,
        `endtime` date NOT NULL,
        `completed` int(11) NOT NULL default '0',
        `status` text character set cp1251 NOT NULL,
        `temp` text collate latin1_general_ci NOT NULL,
        PRIMARY KEY  (`id`)
        ) EN    GINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=15 ;
            */
        }
        public void deleteProject(string _name)
        {
            string deleteProjectCommand = "DROP TABLE " + _name;
            MySqlCommand deleteProjectTask = new MySqlCommand(deleteProjectCommand, globalConnection);
            deleteProjectTask.ExecuteScalar();
            //
            string deleteCommand = "DELETE FROM projects WHERE name='" + _name+"'";
            MySqlCommand deleteTask = new MySqlCommand(deleteCommand, globalConnection);
            deleteTask.ExecuteScalar();
        }

        void readProjects()
        {
            string CommandGetProjects = "SELECT * FROM projects";


            MySqlCommand GetProjectsTable = new MySqlCommand(CommandGetProjects, globalConnection);

            dTable = new DataTable();
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(CommandGetProjects, globalConnection);

            dataAdapter.Fill(dTable);

            DataGridView tempGrid = new DataGridView();
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dTable;



            this.Controls.Add(tempGrid);
            tempGrid.DataSource = bSource;


            comboBoxProjects.Items.Clear();
            Projects = new List<Project>();
            int projectsNum = dTable.Rows.Count;


            for (int i = 0; i <= projectsNum - 1; i++)
            {
                Projects.Add(new Project(Convert.ToInt16(tempGrid.Rows[i].Cells[0].Value),
                    tempGrid.Rows[i].Cells[1].Value.ToString()));
                
                comboBoxProjects.Items.Add(tempGrid.Rows[i].Cells[1].Value.ToString());
                comboBoxProjects.SelectedIndex = i;
            }
            this.Controls.Remove(tempGrid);
            tempGrid = null;
        }
        //

        void deleteTaskFromDB(int _id)
        {
            string deleteCommand = "DELETE FROM "+currentProject+" WHERE id="+_id.ToString();
            MySqlCommand deleteTask = new MySqlCommand(deleteCommand, globalConnection);
            deleteTask.ExecuteScalar();
        }



        private void dataGridView1_Leave(object sender, EventArgs e)
        {
            
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            globalConnection.Close();//При выходе из программы закрываем подключение с БД
            Application.Exit();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
           
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)//Двойное нажатие на задачу в списке задач
        {
            
            if (e.RowIndex <= Tasks.Count && e.RowIndex != -1)
            {
                int indexTask = Convert.ToInt16(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
                Task inputTask = searchTask(indexTask);
                if (inputTask == null) MessageBox.Show("Ошибка чтения задачи из БД!");
                FormShowTask FormTask = new FormShowTask(this, inputTask);
                FormTask.Show();//Открываем окно задачи с выбранной задачей
            }
        }

        private Task searchTask(int _id)
        {
            for (int i = 0; i <= Tasks.Count; i++)
            {
                if (Tasks[i].id == _id) return Tasks[i];
            }
            return null;
        }

        private void новаяЗадачаToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormShowTask NewTaskForm = new FormShowTask(this);
            NewTaskForm.Show();
        }

        private void оПрограммеToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Показать окно About
            MessageBox.Show("Автоматизированная информационная система 'Планирование процесса разработки программных продуктов'\nАнтон Жиляев, «КАЛИНИГРАДСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»\n 2012");
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {

        }

        private void удалитьToolStripMenuItem_Click(object sender, EventArgs e)//Удаление задачи из списка
        {
            int itemIndex = Convert.ToInt16(dataGridView1.SelectedRows[0].Cells[0].Value);


            DialogResult result;
            result = MessageBox.Show("Действительно удалить выбранную задачу?", "Удаление", MessageBoxButtons.YesNo);
            if (result == DialogResult.No)
            {
                //MessageBox.Show("App won´t close");
            }
            if (result == DialogResult.Yes)
            {
                if (CurrentUser == dataGridView1.SelectedRows[0].Cells[2].Value.ToString())
                {
                    deleteTaskFromDB(itemIndex);
                    updateAll();
                }
                else
                {
                    MessageBox.Show("Вы не являетесь создателем этой задачи!\nУдаление невозможно!");
                }
            }
            
        }

        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
        }

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (dataGridView1.Rows[e.RowIndex].Cells[8].Value.ToString() == "Выполнено") e.CellStyle.BackColor = Color.PaleGreen;

            if (dataGridView1.Rows[e.RowIndex].Cells[8].Value.ToString() == "В работе") e.CellStyle.BackColor = Color.DodgerBlue;

            if (dataGridView1.Rows[e.RowIndex].Cells[8].Value.ToString() == "Ожидание") e.CellStyle.BackColor = Color.Moccasin;
        }



        //===========================================
        //Фильтрация списка задач
        int Filter1=0;//по создателю - me,all
        int Filter2=0;//по времени - expired,not expired,all
        int Filter3=0;//по статусу - wait,inwork,completed,all
        //int Filter4=0;//by executor - me,all,*users/////?
        

        void applyFilters()
        {
            applyFilterAllTasks();
            Filter1 = comboBoxFilter1.SelectedIndex;
            Filter2 = comboBoxTime.SelectedIndex;
            Filter3 = comboBoxStatus.SelectedIndex;

            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {
                //Filter1
                if (Filter1 == 0)
                {
                    if (dataGridView1.Rows[i].Cells[2].Value.ToString() != CurrentUser) HideCell(i);
                }
                //Filter2
                if (Filter2 == 0)
                {
                    DateTime currDate = DateTime.Now;
                    DateTime storedDate = Convert.ToDateTime(dataGridView1.Rows[i].Cells[6].Value);
                    int result = DateTime.Compare(storedDate, currDate);
                    if (result > 0) HideCell(i);
                }
                else if (Filter2 == 1)
                {
                    DateTime currDate = DateTime.Now;
                    DateTime storedDate = Convert.ToDateTime(dataGridView1.Rows[i].Cells[6].Value);
                    int result = DateTime.Compare(storedDate, currDate);
                    if (result < 0) HideCell(i);
                }
                //Filter3
                if (Filter3 == 0)
                {
                    if (dataGridView1.Rows[i].Cells[8].Value.ToString() != "Ожидание") HideCell(i);
                }
                else if (Filter3 == 1)
                {
                    if (dataGridView1.Rows[i].Cells[8].Value.ToString() != "В работе") HideCell(i);
                }
                else if (Filter3 == 2)
                {
                    if (dataGridView1.Rows[i].Cells[8].Value.ToString() != "Выполнено") HideCell(i);
                }

                

            }

        }

        void HideCell(int index)
        {
            CurrencyManager cm = (CurrencyManager)BindingContext[dataGridView1.DataSource];
            cm.SuspendBinding();
            dataGridView1.Rows[index].Visible = false;
            cm.ResumeBinding();
        }

        private void comboBoxUsers_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilters();
        }

        void applyFilterAllTasks()
        {

            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {
                    CurrencyManager cm = (CurrencyManager)BindingContext[dataGridView1.DataSource];
                    cm.SuspendBinding();
                    dataGridView1.Rows[i].Visible = true;
                    cm.ResumeBinding();
                
            }
        }

        void applyFilterMineTasks()
        {
            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {
                if (dataGridView1.Rows[i].Cells[2].Value.ToString() == CurrentUser)
                {

                }
                else
                {
                    CurrencyManager cm = (CurrencyManager)BindingContext[dataGridView1.DataSource];
                    cm.SuspendBinding();
                    dataGridView1.Rows[i].Visible = false;
                    cm.ResumeBinding();
                }
            }
        }

        private void comboBoxTime_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilters();
        }

        private void comboBoxStatus_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilters();
        }





        //===============================
        //Открытия окна с диаграммой ганта
        private void диаграммаГантаToolStripMenuItem_Click(object sender, EventArgs e)
        {


            if (dataGridView1.SelectedRows[0].Index <= Tasks.Count && dataGridView1.SelectedRows[0].Index != -1)
            {
                int indexTask = Convert.ToInt16(dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[0].Value);
                Task inputTask = searchTask(indexTask);
                if (inputTask == null) MessageBox.Show("Ошибка чтения задачи из БД!");
                FormDiagram tempform = new FormDiagram(inputTask);
                tempform.Show();
            }


        }

        private void comboBoxProjects_SelectedIndexChanged(object sender, EventArgs e)
        {
           // readAll();
            currentProject = comboBoxProjects.Text;
            if(currentProject!="")readUserTasks();
        }

        private void новыйПроектToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormAddProject NewForm = new FormAddProject(this);
            NewForm.Show();
        }

        private void отчетОВыполненныхПроектахToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormProjectInfo newForm = new FormProjectInfo(this);
            newForm.Show();
        }
        
    }
}
